
SELECT distinct 1 num,ubi.con_anno,ubi.con_id codice_contratto,
 (SELECT a.att_idext
                     FROM attacco a
                    WHERE a.az_id = '01' AND a.att_id = ubi.att_id)
                                                                    POD,
ubi.att_id ATTID,
        s.smis_mat MATSM,
       TRUNC (let.ver_dtfine) data_ultima_lettura,
       misval.fuco_id  "Funzione consumo",
       misval.faco_id FASCIA ,
       misval.lmis_val ultima_lettura,
       misval.lmis_valcon consumo
               
     
       FROM conubifrn ubi, v_lettura let, v_letmisval misval,strmis s,attgmis b

       
    WHERE 
       ubi.cuf_id = let.cuf_id
     
	  and let.CSI_ID = 
                        (SELECT  max(let1.CSI_ID) 
                                 FROM v_lettura let1
                                 WHERE 
                                  let1.az_id = '01'
                                 and let1.sta_idsys <> 1
                                 and let1.sta_idsys <> 17
                                 and let1.sta_idsys <> 18
                                 and ubi.cuf_id=let1.cuf_id
                                 and let1.att_id=let.att_id)
      
      
      AND let.let_id = misval.let_id
    
      and let.att_id = ubi.att_id
  
      and let.az_id = '01'
      and ubi.az_id = '01'
        and let.sta_idsys <> 1
        and let.sta_idsys <> 17
        and let.sta_idsys <> 18
      and misval.az_id = '01'
     and b.az_id='01'
      and s.az_id = '01'
     
   
      and ubi.att_id = b.att_id
      and b.GMIS_ID=s.GMIS_ID
      and misval.SMIS_MAT=s.SMIS_MAT
      
                              and      (select CASE
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper = 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staid
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staidsuper
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper <> 0
                         THEN master_ista.iss_sstaidsuper
                      ELSE -1
                   END
                       
             FROM  ista master_ista
            WHERE master_ista.az_id = b.az_id
              AND master_ista.csi_id = b.csi_id
              AND master_ista.ista_verstato = b.ista_verstato)     = 2 
                               and b.ver_dtfine = to_date ('31129999','ddmmyyyy')

                                     and      (select CASE
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper = 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staid
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staidsuper
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper <> 0
                         THEN master_ista.iss_sstaidsuper
                      ELSE -1
                   END
                       
             FROM  ista master_ista
            WHERE master_ista.az_id = s.az_id
              AND master_ista.csi_id = s.csi_id
              AND master_ista.ista_verstato = s.ista_verstato)     = 2 
                               and s.ver_dtfine = to_date ('31129999','ddmmyyyy')
                              and s.tsm_id <> 30
                           
         and ubi.ver_dtfine = to_date ('31129999','ddmmyyyy')
        and      (select CASE
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper = 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staid
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staidsuper
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper <> 0
                         THEN master_ista.iss_sstaidsuper
                      ELSE -1
                   END
                  
                  
             FROM  ista master_ista
            WHERE master_ista.az_id = ubi.az_id
              AND master_ista.csi_id = ubi.csi_id
              AND master_ista.ista_verstato = ubi.ista_verstato)     = 2    
      
   --and ubi.att_id =66433;
      
      
       union all
     
      
      
             
       SELECT distinct 1 num,ubi.con_anno,ubi.con_id codice_contratto,
 (SELECT a.att_idext
                     FROM attacco a
                    WHERE a.az_id = '01' AND a.att_id = ubi.att_id)
                                                                    POD,
ubi.att_id ATTID,
        s.smis_mat MATSM,
       TRUNC (let.ver_dtfine) data_ultima_lettura,
       misval.fuco_id  "Funzione consumo",
       misval.faco_id FASCIA ,
       misval.CVAL_VALLETATT ultima_lettura,
       misval.cval_val consumo 
               
     
       FROM conubifrn ubi, v_consumo let, v_conval_consumo misval,attgmis b,strmis s

       
    WHERE 
       ubi.cuf_id = let.cuf_id
  
      
---MODIFICA FATTA DA LUCA IN DATA 14.03.2012: TOLTO IL CONTROLLO SUL ROWID E MESSO QUELLO DEL CSI_ID
	  and let.CSI_ID = 
                        (SELECT  max(let1.CSI_ID) 
                                 FROM v_consumo let1
                                 WHERE 
                                  let1.az_id = '01'
                                 and let1.sta_idsys <> 1
                                 and let1.sta_idsys <> 17
                                 and let1.sta_idsys <> 18
                                 and ubi.cuf_id=let1.cuf_id
                                 and let1.att_id=let.att_id)
      
      
      AND let.cons_id = misval.cons_id
   
      and let.att_id = ubi.att_id
     
      and let.az_id = '01'
      and ubi.az_id = '01'
        and let.sta_idsys <> 1
        and let.sta_idsys <> 17
        and let.sta_idsys <> 18
      and misval.az_id = '01'
      and b.az_id = '01'
      and s.az_id = '01'
     
      and ubi.att_id=b.att_id
                                 and b.GMIS_ID=s.GMIS_ID
                              
                                and      (select CASE
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper = 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staid
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staidsuper
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper <> 0
                         THEN master_ista.iss_sstaidsuper
                      ELSE -1
                   END
                  
                  
             FROM  ista master_ista
            WHERE master_ista.az_id = b.az_id
              AND master_ista.csi_id = b.csi_id
              AND master_ista.ista_verstato = b.ista_verstato)     = 2 
                                and b.ver_dtfine = to_date ('31129999','ddmmyyyy')
                                     and      (select CASE
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper = 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staid
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staidsuper
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper <> 0
                         THEN master_ista.iss_sstaidsuper
                      ELSE -1
                   END
                  
                  
             FROM  ista master_ista
            WHERE master_ista.az_id = s.az_id
              AND master_ista.csi_id = s.csi_id
              AND master_ista.ista_verstato = s.ista_verstato)     = 2 
                               and s.ver_dtfine = to_date ('31129999','ddmmyyyy')
                              and s.tsm_id = 30
                              
         and ubi.ver_dtfine = to_date ('31129999','ddmmyyyy')
        and      (select CASE
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper = 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staid
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper = 0
                         THEN master_ista.iss_staidsuper
                      WHEN master_ista.iss_staid <> 0
                      AND master_ista.iss_staidsuper <> 0
                      AND master_ista.iss_sstaidsuper <> 0
                         THEN master_ista.iss_sstaidsuper
                      ELSE -1
                   END
                  
                  
             FROM  ista master_ista
            WHERE master_ista.az_id = ubi.az_id
              AND master_ista.csi_id = ubi.csi_id
              AND master_ista.ista_verstato = ubi.ista_verstato)     = 2         

     